#!/usr/bin/env python
# coding=utf-8
"""
@Author  : youjia - 卞志伟
@file    : week_report_zg_recv_mail.py
@contact : bianzhiwei@iyoujia.com
@time    : 2019-08-06 16:17 
@Desc    : 
@Software: PyCharm
"""
import os
import sys

# 当前文件的路径
pwd, filename = os.path.split(os.path.abspath(__file__))
# 当前文件的父路径
father_path = os.path.abspath(os.path.dirname(pwd) + os.path.sep + ".")
# 当前文件的前两级目录
grader_father = os.path.abspath(os.path.dirname(pwd) + os.path.sep + "..")
sys.path.append(pwd)
sys.path.append(father_path)
sys.path.append(grader_father)

import pandas as pd
from report_system.utils import db_util
from report_system.utils import date_util
from report_system.utils import mail_util
from report_system.utils import excel_util
from report_system.utils.log_util import log
from email.mime.multipart import MIMEMultipart
from report_system.utils import development_util
from report_system.utils.development_util import debug

temp = {'渠道名称': '有家民宿',
        '评价内容': '床又大又软，躺上去特别舒服，装修的也很雅致，总之，这次的体验感还不错',
        '房源ID': 6483319,
        '房源名称': 'U+近观音桥北城天街九街童年记忆大三居',
        '房东ID': 127898415,
        '城市': '重庆',
        '订单ID': 11674612,
        '总体评分': 50,
        '分项_交通便利评分': 50,
        '分项_沟通交流评分': 50,
        '分项_卫生状况评分': 50,
        '分项_地理位置评分': 50,
        '分项_性价比评分': 50.0}


def approximate_score(score):
    try:
        return int(score) / 10
    except:
        return None


def process(mail_sub='KA周报'):
    mail_files = mail_util.recv_mail(mail_sub)
    log.info("收到的邮件:{}".format(mail_files))
    select_sql = """select bo.id order_id,
        bo.lodgeunit_id lodge_id,
        bo.third_id third_order_id,
        date(bo.checkin_day) checkin_day
        from youjia.orders bo
        left join youjia.comment ct on ct.order_id = bo.id and ct.comment_type = 0 and auto_comment = 0
        where bo.third_id = "{third_id}"
        and length(bo.third_id)>5 
        and ct.id is null
        and bo.cancel_status = 0
        and bo.order_channel=25
        order by bo.id desc limit 0, 1"""

    insert_sql = """insert into youjia.comment (lodge_id, order_id, create_time, 
                    audit_state, score, content, sanitation_s, service_s, traffic_s,
                    facilities_s, environment_s, comment_type, auto_comment, 
                    pf_comment_id, platform_id, check_in_time) 
                    values(:p0,:p1,:p2,:p3,:p4,:p5,:p6,:p7,:p8,:p9,:p10,:p11,:p12,:p13,:p14,:p15)"""
    if isinstance(mail_files, list) and mail_files.__len__() > 0:
        with db_util.get_yj_base_db_conn() as conn:
            for mail_file in mail_files:
                if '月' in mail_file:
                    if '有家' in mail_file:
                        sheet_name = '评价维度'
                    else:
                        sheet_name = '评级维度'
                else:
                    sheet_name = '评价维度'
                excel_dict = {"评价维度": pd.read_excel(mail_file, sheet_name)}
                # excel_dict, random_color_dict = excel_util.excel_to_df(mail_file)
                print(excel_dict)
                df = excel_dict['评价维度'] if '评价维度' in excel_dict else None
                print(df)
                if not isinstance(df, pd.DataFrame):
                    msg = '榛果评论入库失败！ data@iyoujia.com邮箱未接收到相关邮件'
                    log.exception(msg)
                    mail_util.error_mail(msg, '')
                    return
                for idx, row in df.iterrows():
                    row_dict = dict(row)
                    auto_comment = comment_type = 0
                    content = row_dict['评价内容']
                    third_order_id = row_dict['订单ID']
                    log.info("榛果订单:{}".format(third_order_id))
                    score = approximate_score(row_dict['总体评分'])
                    traffic_s = approximate_score(row_dict['分项_交通便利评分'])
                    service_s = approximate_score(row_dict['分项_沟通交流评分'])
                    sanitation_s = approximate_score(row_dict['分项_卫生状况评分'])
                    environment_s = approximate_score(row_dict['分项_地理位置评分'])
                    facilities_s = approximate_score(row_dict['分项_性价比评分'])
                    bo_rows = db_util.records_select(select_sql.format(third_id=third_order_id))
                    if bo_rows.__len__() == 0 or not isinstance(bo_rows, list):
                        log.info("未查询到榛果订单 {} 未评价的相关有家订单".format(third_order_id))
                        continue
                    bo_row = bo_rows[0]
                    # print(row_dict)
                    order_id = bo_row['order_id']
                    lodge_id = bo_row['lodge_id']
                    log.info("查询到未评价有家相关订单为:{}".format(order_id))
                    log.info("其相关房源为:{}".format(lodge_id))
                    checkin_day = bo_row['checkin_day']
                    conn.query(insert_sql,
                               p0=lodge_id,
                               p1=order_id,
                               p2=date_util.datetime_now(),
                               p3=2,
                               p4=score,
                               p5=content,
                               p6=sanitation_s,
                               p7=service_s,
                               p8=traffic_s,
                               p9=facilities_s,
                               p10=environment_s,
                               p11=comment_type,
                               p12=auto_comment,
                               p13=None,
                               p14=25,
                               p15=checkin_day)

    else:
        mail_util.error_mail("【榛果民宿】-KA周报   未接收到相关邮件")


if __name__ == '__main__':
    process()
